--select * from fn_update_view_cost_by_flow ( 1, 2009 ) order by Process_Name_ID, PS_Type, Part_Number

ALTER  function fn_update_view_cost_by_flow_and_location ( @product_id int, @year_of_txn int )
 returns @fn_results Table(ProcessStep_ID bigint,		
 			  PS_Type varchar(20),
 			  Location_ID int,
 			  Location varchar(80),
			  Part_Number varchar(50),
			  Process_Name  varchar(50),
			  Process_Name_ID int,
			  BasePart_Number varchar(50),
 			  Q1_Standard_Cost NUMERIC(18,4),
 			  Q1_Yield float,
 			  Q2_Standard_Cost NUMERIC(18,4),
 		 	  Q2_Yield float,
 			  Q3_Standard_Cost NUMERIC(18,4),
 		 	  Q3_Yield float,
 			  Q4_Standard_Cost NUMERIC(18,4),
 			  Q4_Yield float,
			  Projected_Cost NUMERIC(18,4),
			  Projected_Yield float
 )
 AS
 begin
 declare @results Table(ProcessStep_ID bigint,		
 			  PS_Type varchar(20),
 			  Location_ID int,
 			  Location varchar(80),
			  Part_Number varchar(50),
			  Process_Name  varchar(50),
			  Process_Name_ID int,
			  BasePart_Number varchar(50),
 			  Q1_Standard_Cost NUMERIC(18,4),
 			  Q1_Yield float,
 			  Q2_Standard_Cost NUMERIC(18,4),
 		 	  Q2_Yield float,
 			  Q3_Standard_Cost NUMERIC(18,4),
 		 	  Q3_Yield float,
 			  Q4_Standard_Cost NUMERIC(18,4),
 			  Q4_Yield float,
			  Projected_Cost NUMERIC(18,4),
			  Projected_Yield float
 )
 
 	declare @flow_cursor CURSOR
 	declare @location_cursor CURSOR
 	declare @location_name varchar(80)
 	declare @temp_ps_type varchar(20)
 	declare @temp_ps_type_name varchar(20)
 	declare @temp_process_step_id bigint
 	declare @temp_process_step_id1 bigint
 	declare @temp_location_id int
	declare @productName varchar(50)
    declare @baseProductName varchar(50)
 
 	declare @temp_Q1_Standard_Cost NUMERIC(18,4)
 	declare @temp_Q1_Yield float
 	declare @temp_Q2_Standard_Cost NUMERIC(18,4)
 	declare @temp_Q2_Yield float
 	declare @temp_Q3_Standard_Cost NUMERIC(18,4)
 	declare @temp_Q3_Yield float
 	declare @temp_Q4_Standard_Cost NUMERIC(18,4)
 	declare @temp_Q4_Yield float
 	declare @temp_Fin_Year datetime
 	declare @rows_count int
	declare @Process_Name_Id int		
	declare @Parent_P_Type varchar(50)
	DECLARE @temp_Projected_Cost NUMERIC(18,4),	@temp_Projected_Yield FLOAT

 	set @rows_count = -1
 
 	set @temp_process_step_id = (select ProcessStep_ID from Product where Product_ID = @product_id)
	select @baseProductName = Part_Number, @Parent_P_Type = ISNULL( P_Type, 'CHIP') from Product where Product_ID = @product_id

	IF ( @Parent_P_Type = 'CHIP' )
	BEGIN
		set @flow_cursor = CURSOR FOR
		SELECT PS.ProcessStep_ID, PS.PS_Type, PS.PS_Type_Name[Process_Type_Name], P.Part_Number FROM Product P INNER JOIN Process_Step PS ON P.ProcessStep_ID = PS.ProcessStep_ID INNER JOIN Process_Step_Traverse_Table PSTT ON PS.ProcessStep_ID = PSTT.ProcessStep_ID WHERE (P.P_Parent_Product_ID = @product_id) UNION SELECT PS.ProcessStep_ID, PS.PS_Type, PS.PS_Type_Name[Process_Type_Name], P.Part_Number FROM  Product P INNER JOIN Process_Step PS ON P.ProcessStep_ID = PS.ProcessStep_ID INNER JOIN Process_Step_Traverse_Table PSTT ON PS.ProcessStep_ID = PSTT.Next_ProcessStep_ID WHERE (P.P_Parent_Product_ID = @product_id)
	END
	ELSE
	BEGIN
		set @flow_cursor = CURSOR FOR
		select Process_Step.ProcessStep_ID, PS_Type, PS_Type_Name[Process_Type_Name],Part_Number from Process_Step, Product where Process_Step.ProcessStep_ID = Product.ProcessStep_ID and Product_Id = @product_id		
	END	

 	open @flow_cursor
	fetch next from @flow_cursor into @temp_process_step_id, @temp_ps_type, @temp_ps_type_name, @productName
	while @@fetch_status = 0
	begin
			
		set @Process_Name_Id = 
		CASE 
			WHEN (SELECT SORTING_ORDER FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = @temp_ps_type) > 0 
				THEN (SELECT SORTING_ORDER FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = @temp_ps_type)
				
				ELSE 0
		END
		
		SET @location_cursor = CURSOR FOR
		SELECT ProcessStep_ID, Location_ID FROM Process_Step_Location_Table WHERE  Processstep_Id = @temp_process_step_id
		OPEN @location_cursor
		FETCH next FROM @location_cursor INTO @temp_process_step_id1, @temp_location_id  
		WHILE @@fetch_status = 0
		BEGIN
 			SET @location_name = (SELECT l.L_Vendor_Name FROM Location l, Address a WHERE l.Location_ID = @temp_location_id and l.Address_ID = a.Address_ID)
 			SELECT @rows_count = count(processstep_id) FROM update_view_cost_table WHERE processstep_id = @temp_process_step_id1 and location_id = @temp_location_id AND YEAR(fin_year) = @year_of_txn
 			IF @rows_count = 0
 			BEGIN
				 insert into @results 
				(ProcessStep_ID, PS_Type, Location_ID, Location, Part_Number, Process_Name, Process_Name_ID, BasePart_Number,  
				Q1_Standard_Cost, Q1_Yield, 
				Q2_Standard_Cost, Q2_Yield,
				Q3_Standard_Cost, Q3_Yield,
				Q4_Standard_Cost, Q4_Yield,
				Projected_Cost,	  Projected_Yield ) 
				values 
				(@temp_process_step_id1, @temp_ps_type, @temp_location_id, @location_name, @productName, @temp_ps_type_name, @Process_Name_Id, @baseProductName,
				0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0,0.0,0.0
				)
			END
			ELSE
			BEGIN
				SELECT 
				@temp_Q1_Standard_Cost = ISNULL(Q1_Standard_Cost,0), @temp_Q1_Yield = ISNULL(Q1_Yield,0), 
				@temp_Q2_Standard_Cost = ISNULL(Q2_Standard_Cost,0), @temp_Q2_Yield = ISNULL(Q2_Yield,0), 
				@temp_Q3_Standard_Cost = ISNULL(Q3_Standard_Cost,0), @temp_Q3_Yield = ISNULL(Q3_Yield,0), 
				@temp_Q4_Standard_Cost = ISNULL(Q4_Standard_Cost,0), @temp_Q4_Yield = ISNULL(Q4_Yield,0),
				@temp_Projected_Cost   = ISNULL(Projected_Cost,0), @temp_Projected_Yield = ISNULL(Projected_Yield,0), 
				@temp_Fin_Year = Fin_Year FROM Update_View_Cost_Table 
				where ProcessStep_ID = @temp_process_step_id1 and Location_id = @temp_location_id and year(fin_year) = @year_of_txn
 
				insert into @results 
				(ProcessStep_ID, PS_Type, Location_ID, Location, Part_Number, Process_Name, Process_Name_ID, BasePart_Number,  
				Q1_Standard_Cost,Q1_Yield, 
				Q2_Standard_Cost,Q2_Yield,
				Q3_Standard_Cost,Q3_Yield,
				Q4_Standard_Cost,Q4_Yield,
				Projected_Cost,	  Projected_Yield ) 
				values 
				(@temp_process_step_id, @temp_ps_type, @temp_location_id, @location_name, @productName, @temp_ps_type_name, @Process_Name_Id, @baseProductName,
				@temp_Q1_Standard_Cost,@temp_Q1_Yield, 
				@temp_Q2_Standard_Cost,@temp_Q2_Yield, 
				@temp_Q3_Standard_Cost,@temp_Q3_Yield, 
				@temp_Q4_Standard_Cost,@temp_Q4_Yield,
				@temp_Projected_Cost,	@temp_Projected_Yield
				)
 		
			END

			FETCH next from @location_cursor into @temp_process_step_id1, @temp_location_id  
		END
		CLOSE @location_cursor
		DEALLOCATE @location_cursor			
 		fetch next from @flow_cursor into @temp_process_step_id, @temp_ps_type, @temp_ps_type_name, @productName
 	END
 	close @flow_cursor
 	deallocate @flow_cursor
 
 	--insert @fn_results select *from @results
	insert @fn_results select * from @results
		group by Process_Name, Location_ID,Location,Part_Number,processstep_Id,PS_Type,Process_Name_ID,BasePart_Number,
		Q1_Standard_Cost,Q1_Yield,
		Q2_Standard_Cost,Q2_Yield,
		Q3_Standard_Cost,Q3_Yield,
		Q4_Standard_Cost,Q4_Yield,Projected_Cost, Projected_Yield
 	return
 end
